package dao;

import vo.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.UUID;

public class UserD extends BaseD {

    public User checkAccount(String user, String password) throws Exception {
        initConnection();
        Statement stat = conn.createStatement();
        String sql = "select * from user where id = '" + user + "' and password = '" + password + "'";
        ResultSet rs = stat.executeQuery(sql);
        User stu = getUser(rs);
        closeConnection();
        return stu;
    }

    public User findWithId(String id) throws Exception{
        initConnection();
        Statement stat = conn.createStatement();
        String sql = "select * from user where id = '" + id + "'";
        ResultSet rs = stat.executeQuery(sql);
        User stu = getUser(rs);
        closeConnection();
        return stu;
    }

    public ArrayList<User> findWithName(String name) throws Exception{
        ArrayList<User> al = new ArrayList<>();
        initConnection();
        Statement stat = conn.createStatement();
        String sql = "select * from user where name = '" + name + "'";
        ResultSet rs = stat.executeQuery(sql);
        getMoreUser(al, rs);
        closeConnection();
        return al;
    }

    public User createUserAccount(String name,String password, String email) throws Exception{
        initConnection();
        String sql = "insert into user(id, name, password, email) values(?,?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, name);
        ps.setString(2, name);
        ps.setString(3, password);
        ps.setString(4, email);
        int i = ps.executeUpdate();
        closeConnection();
        User user = new User();
        user.setId(name);
        user.setName(name);
        user.setPassword(password);
        user.setEmail(email);
        return i==1?user:null;
    }

    public boolean insertUser(String id, String name, String sex) throws Exception{
        initConnection();
        String sql = "insert into user(id, name, sex) values(?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, id);
        ps.setString(2, name);
        ps.setString(3, sex);
        int i = ps.executeUpdate();
        closeConnection();
        return i == 1;
    }

    public boolean deleteUser(String id) throws Exception{

        initConnection();
        Statement stat = conn.createStatement();
        String sql = "delete from user where id='"+id+"'";
        int i = stat.executeUpdate(sql);
        closeConnection();
        return i==1;
    }

    public ArrayList<User> getOnePage(int page, int size) throws Exception{
        ArrayList<User> al = new ArrayList<>();
        initConnection();
        String sql = "SELECT * FROM user limit ?, ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, (page-1)*size);
        ps.setInt(2, size);
        ResultSet rs =  ps.executeQuery();
        getMoreUser(al, rs);
        closeConnection();
        return al;
    }

    public int getUserCount() throws Exception{
        initConnection();
        String sql = "select count(*) from user";
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery(sql);
        rs.next();
        int count = rs.getInt(1);
        closeConnection();
        return count;
    }

    public void updateUserInfo(String id, String name, String sex) throws Exception{

        initConnection();
        String sql = "update user set name=?, sex=? where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, name);
        ps.setString(2, sex);
        ps.setString(3, id);
        ps.executeUpdate();
        closeConnection();
    }

    public void updateUserSecurity(String id, String email, String password) throws Exception{

        initConnection();
        String sql = "update user set password=?, email=? where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, password);
        ps.setString(2, email);
        ps.setString(3, id);
        ps.executeUpdate();
        closeConnection();
    }

    private User getUser(ResultSet rs) throws SQLException {
        User stu = null;
        if (rs.next()){
            stu = new User();
            stu.setId(rs.getString("id"));
            stu.setPassword(rs.getString("password"));
            stu.setName(rs.getString("name"));
            stu.setSex(rs.getString("sex"));
            stu.setEmail(rs.getString("email"));
        }
        return stu;
    }

    private void getMoreUser(ArrayList<User> al, ResultSet rs) throws SQLException {
        while (rs.next()){
            User stu = new User();
            stu.setId(rs.getString("id"));
            stu.setPassword(rs.getString("password"));
            stu.setName(rs.getString("name"));
            stu.setSex(rs.getString("sex"));
            stu.setEmail(rs.getString("email"));
            al.add(stu);
        }
    }


}
